package com.wcgksoft.controller.Patrol;

import com.google.gson.JsonObject;
import com.wcgksoft.common.charts.component.ChartsBarDataBean;
import com.wcgksoft.common.factory.ChartsFactory;
import com.wcgksoft.common.util.Layui;
import com.wcgksoft.common.util.MsgInfo;
import com.wcgksoft.entity.system.EnterpriseProject;
import com.wcgksoft.entity.system.SysUser;
import com.wcgksoft.service.Patrol.PatrolAccountService;
import com.wcgksoft.service.openAccount.OpenAccountService;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import org.apache.shiro.SecurityUtils;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName StatisticsController
 * @Description TODO
 * @Author 王宁
 * @Date 2020/3/22 15:20
 * @Version 1.0
 **/
@RequestMapping("statistics")
@Controller
public class StatisticsController {

    @Autowired
    private PatrolAccountService patrolAccountService;

    @Autowired
    private OpenAccountService openAccountService;

    @ResponseBody
    @RequestMapping(value = "findAllPatrol")
    public MsgInfo findAllPatrol() {
        MsgInfo msgInfo = new MsgInfo();
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        List<Object[]> list6 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT CASE datas.weeks  WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' WHEN '4' THEN '4' WHEN '5' THEN '5' WHEN '6' THEN '6' WHEN '7' THEN '7' WHEN '8' THEN '8' WHEN '9' THEN '9' WHEN '10' THEN '10' WHEN '11' THEN '11' WHEN '12' THEN '12' END AS 'week', IFNULL( datas.totalUser, 0 ) totalUser  FROM " +
                " (SELECT a.d weeks, b.totalUser FROM (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL select 8 union all select 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) a LEFT JOIN ( SELECT date_format( us.constructionTime, '%m' ) AS datetime, COUNT( us.id ) AS totalUser  FROM t_patrolaccount us WHERE status<>'2' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and YEAR ( us.constructionTime )= YEAR (NOW()) GROUP BY datetime) b ON a.d = b.datetime) datas ORDER BY datas.weeks ASC").list();
        List<Object[]> list7 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT CASE datas.weeks WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' WHEN '4' THEN '4' WHEN '5' THEN '5' WHEN '6' THEN '6' WHEN '7' THEN '7' WHEN '8' THEN '8' WHEN '9' THEN '9' WHEN '10' THEN '10' WHEN '11' THEN '11' WHEN '12' THEN '12' END AS 'week', IFNULL( datas.totalUser, 0 ) totalUser  " +
                "FROM (SELECT a.d weeks,b.totalUser FROM ( SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) a LEFT JOIN ( SELECT " +
                "date_format( us.publishTime, '%m' ) AS datetime, COUNT( us.id ) AS totalUser FROM t_patrolaccount us  WHERE status='2' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and YEAR ( us.publishTime )= YEAR ( NOW()) GROUP BY datetime) b ON a.d = b.datetime) datas ORDER BY datas.weeks ASC").list();
        String[] data1 = new String[list6.size()];
        String[] month = new String[]{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"};
        HashMap<String, String[]> map = new HashMap<>();
        String[] data2 = new String[list7.size()];
        for (int i = 0; i < list6.size(); i++) {
            data1[i] = list6.get(i)[1].toString();
        }
        for (int i = 0; i < list7.size(); i++) {
            data2[i] = list7.get(i)[1].toString();
        }
        map.put("month", month);
        //已完成
        map.put("com", data1);
        //未完成
        map.put("unCom", data2);
        msgInfo.setData(map);
        return msgInfo;
    }

    /**
     * 巡查统计 豆腐块 admin
     *
     * @param modelMap
     * @param
     * @param
     * @return
     * @throws ParseException
     */
    @RequestMapping("count")
    public String count(ModelMap modelMap) {
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        //已完成当日
        List list1 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status<>'2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and to_days(constructionTime)=to_days(now());").list();
        modelMap.put("day", list1.get(0));
        //查询当月
        List list2 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status<>'2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(constructionTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');").list();
        modelMap.put("month", list2.get(0));
        //查询当年已完成
        List list3 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status<>'2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(constructionTime,'%Y')=DATE_FORMAT(CURDATE(),'%Y');").list();
        modelMap.put("year", list3.get(0));

        //未完成当日
        List list7 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status='2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and to_days(publishTime)=to_days(now());").list();
        modelMap.put("unCompleteDay", list7.get(0));
        //当月未完成
        List list4 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status='2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(publishTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');").list();
        modelMap.put("unCompleteMonth", list4.get(0));
        //当年未完成
        List list5 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status='2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(publishTime,'%Y')=DATE_FORMAT(CURDATE(),'%Y');").list();
        modelMap.put("unCompleteYear", list5.get(0));


        return "patrol/patrolView";
    }


    /**
     * 施工角色豆腐块
     * @return
     */
    @ResponseBody
    @RequestMapping("findSomeCount")
    public MsgInfo findSomeCount(){
        MsgInfo msgInfo = new MsgInfo();
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        Map<String, Object> map = new HashMap<>();

        List list6 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status<>'2' and proId='" + sysUser.getProId() + "' and receiverId=' " + sysUser.getId() +  "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and to_days(constructionTime)=to_days(now());").list();
        map.put("day1", list6.get(0));
        //查询当月
        List list7 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status<>'2' and proId='" + sysUser.getProId() + "' and receiverId=' " + sysUser.getId() +  "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(constructionTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');").list();
        map.put("month1", list7.get(0));
        //查询当年已完成
        List list8 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status<>'2' and proId='" + sysUser.getProId() + "' and receiverId=' " + sysUser.getId() +  "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(constructionTime,'%Y')=DATE_FORMAT(CURDATE(),'%Y');").list();
        map.put("year1", list8.get(0));
        //当天未完成
        List list11 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status='2' and proId='" + sysUser.getProId() + "' and receiverId=' " + sysUser.getId() +  "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and to_days(publishTime)=to_days(now());").list();
        map.put("unCompleteDay1", list11.get(0));
        //当月未完成
        List list9 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status='2' and proId='" + sysUser.getProId() + "' and receiverId=' " + sysUser.getId() +  "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(publishTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');").list();
        map.put("unCompleteMonth1", list9.get(0));
        //当年未完成
        List list10 = patrolAccountService.getHibernateSession().createSQLQuery("select COUNT(*) from t_patrolaccount where status='2' and proId='" + sysUser.getProId() + "' and receiverId=' " + sysUser.getId() +  "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "'  and DATE_FORMAT(publishTime,'%Y')=DATE_FORMAT(CURDATE(),'%Y');").list();
        map.put("unCompleteYear1", list10.get(0));

        msgInfo.setData(map);
        return msgInfo;
    }


    /**
     * 管理巡查当月每日
     * @return
     */
    @ResponseBody
    @RequestMapping("findAdminPatrolEChart")
    public MsgInfo findAdminPatrolEChart(){
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        //当月每日
        List<Object[]> list7 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT d,IFNULL( b.pid, 0 ) pid FROM" +
                " ( SELECT ADDDATE( y.FIRST, x.d - 1 ) AS d  FROM ( SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL " +
                "  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL " +
                "  SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL " +
                "  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL " +
                "  SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL " +
                "  SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 ) x, (SELECT date( now()) - INTERVAL DAY (date( " +
                "  now())) - 1 DAY AS FIRST, DAY ( LAST_DAY( date( now()))) AS last) y WHERE x.d <= y.last) a LEFT JOIN (SELECT " +
                "  date( p.constructionTime ) AS datetime, COUNT( p.id ) AS pid FROM t_patrolaccount p WHERE " +
                "  status<>'2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "' " +
                " GROUP BY date(p.constructionTime)) b ON a.d = b.datetime ORDER BY date(a.d) ASC").list();
        String[] day = new String[list7.size()];
        String[] data = new String[list7.size()];
        String[] month=new String[list7.size()];
        for (int i = 0; i < list7.size(); i++) {
            day[i] = list7.get(i)[0].toString().substring(8).replaceFirst("^0*","")+"日";
            data[i] = list7.get(i)[1].toString();
            month[i]=list7.get(i)[0].toString().substring(5,7).replaceFirst("^0*","");
        }
        MsgInfo msgInfo = new MsgInfo();
        Map<String, String[]> map = new HashMap<>();
        map.put("monthDay",data);
        map.put("monthDayTime",day);
        map.put("month",month);
        msgInfo.setData(map);
        return msgInfo;
    }

    /**
     * 当年每月管理
     * @return
     */
    @ResponseBody
    @RequestMapping("findYearMonthAdmin")
    public MsgInfo findYearMonthAdmin(){
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        List<Object[]> list6 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT CASE datas.weeks  " +
                "  WHEN '1' THEN '1'  WHEN '2' THEN '2'  WHEN '3' THEN '3' WHEN '4' THEN '4'  " +
                "  WHEN '5' THEN '5'  WHEN '6' THEN '6'  WHEN '7' THEN '7' WHEN '8' THEN '8'  " +
                "  WHEN '9' THEN '9'  WHEN '10' THEN '10'  WHEN '11' THEN '11'  " +
                "  WHEN '12' THEN '12'  END AS 'week', IFNULL( datas.totalUser, 0 ) totalUser  FROM (SELECT a.d weeks, b.totalUser  FROM ( " +
                "  SELECT " +
                "   1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT " +
                "   6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT " +
                "   11 UNION ALL SELECT " +
                "   12 ) a LEFT JOIN ( SELECT date_format( us.constructionTime, '%m' ) AS datetime, COUNT( us.id ) AS totalUser  FROM t_patrolaccount us  WHERE " +
                " status<>'2' and proId=' " + sysUser.getProId() + "' and enterpriseId=' " + sysUser.getEnterprise().getId() + "' and areaId=' " + enterpriseProject.getId() + "'" +
                "  and YEAR ( us.constructionTime )= YEAR ( NOW())  GROUP BY datetime) b ON a.d = b.datetime) datas ORDER BY datas.weeks ASC").list();

        String[] month = new String[list6.size()];
        String[] data1 = new String[list6.size()];
        for (int i = 0; i < list6.size(); i++) {
            month[i] = list6.get(i)[0].toString()+"月";
            data1[i] = list6.get(i)[1].toString();
        }
        MsgInfo msgInfo = new MsgInfo();
        Map<String, String[]> map = new HashMap<>();
        map.put("yearMonth",data1);
        map.put("yearMonthTime",month);
        msgInfo.setData(map);
        return msgInfo;
    }
    /**
     * 施工队当月每日
     * @return
     */
    @ResponseBody
    @RequestMapping("findConstructionPatrolByDay")
    public MsgInfo findConstructionPatrol(){
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        MsgInfo msgInfo = new MsgInfo();
        HashMap<String, String[]> map = new HashMap<>();
        //当月每日
        List<Object[]> list7 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT d,IFNULL( b.pid, 0 ) pid FROM" +
                    " ( SELECT ADDDATE( y.FIRST, x.d - 1 ) AS d  FROM ( SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL " +
                    "  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL " +
                    "  SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL " +
                    "  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL " +
                    "  SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL " +
                    "  SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 ) x, (SELECT date( now()) - INTERVAL DAY (date( " +
                    "  now())) - 1 DAY AS FIRST, DAY ( LAST_DAY( date( now()))) AS last) y WHERE x.d <= y.last) a LEFT JOIN (SELECT " +
                    "  date( p.constructionTime ) AS datetime, COUNT( p.id ) AS pid FROM t_patrolaccount p WHERE " +
                "  status<>'2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and receiverId='" + sysUser.getId() + "' and areaId='" + enterpriseProject.getId() + "' " +
                " GROUP BY " +
                "  date(p.constructionTime)  " +
                " ) b ON a.d = b.datetime  " +
                "ORDER BY " +
                " date(a.d) ASC").list();
        String[] month = new String[list7.size()];
        String[] day = new String[list7.size()];
        String[] data = new String[list7.size()];
        for (int i = 0; i < list7.size(); i++) {
            day[i] = list7.get(i)[0].toString().substring(8).replaceFirst("^0*","")+"日";
            data[i] = list7.get(i)[1].toString();
            month[i]=list7.get(i)[0].toString().substring(5,7).replaceFirst("^0*","");
        }
        map.put("day",day);
        map.put("data",data);
        map.put("month",month);
        msgInfo.setData(map);
        return msgInfo;
    }

    @ResponseBody
    @RequestMapping("findConstructionPatrolByMonth")
    public MsgInfo findConstructionPatrolByMonth(){
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        MsgInfo msgInfo = new MsgInfo();
        Map<String, String[]> map = new HashMap<>();
        ////当年每月
        List<Object[]> list6 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT CASE datas.weeks  " +
                "  WHEN '1' THEN '1'  WHEN '2' THEN '2'  WHEN '3' THEN '3' WHEN '4' THEN '4'  " +
                "  WHEN '5' THEN '5'  WHEN '6' THEN '6'  WHEN '7' THEN '7' WHEN '8' THEN '8'  " +
                "  WHEN '9' THEN '9'  WHEN '10' THEN '10'  WHEN '11' THEN '11'  " +
                "  WHEN '12' THEN '12'  END AS 'week', IFNULL( datas.totalUser, 0 ) totalUser  FROM (SELECT a.d weeks, b.totalUser  FROM ( " +
                "  SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT " +
                "6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT " +
                "  11 UNION ALL SELECT " +
                "  12 ) a LEFT JOIN ( SELECT date_format( us.constructionTime, '%m' ) AS datetime, COUNT( us.id ) AS totalUser  FROM t_patrolaccount us  WHERE " +
                "  status<>'2' and proId=' " + sysUser.getProId() + "' and receiverId='" + sysUser.getId() + "' and enterpriseId=' " + sysUser.getEnterprise().getId() + "' and areaId=' " + enterpriseProject.getId() + "'" +
                "  and YEAR ( us.constructionTime )= YEAR (NOW()) GROUP BY datetime) b ON a.d = b.datetime  " +
                " ) datas ORDER BY datas.weeks ASC").list();
        String[] month = new String[list6.size()];
        String[] data1 = new String[list6.size()];
        for (int i = 0; i < list6.size(); i++) {
            month[i] = list6.get(i)[0].toString()+"月";
            data1[i] = list6.get(i)[1].toString();
        }
        map.put("month",month);
        map.put("data1",data1);
        msgInfo.setData(map);
        return msgInfo;
    }


    @ResponseBody
    @RequestMapping("findPatrolByMonth")
    public MsgInfo findPatrolByTeam(@RequestParam(value = "teamId", required = false) String teamId,
                                    @RequestParam(value = "timeDate", required = false) String timeDate){
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        List<Object[]> list8 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT CASE datas.weeks  " +
                "  WHEN '1' THEN '1'  WHEN '2' THEN '2'  WHEN '3' THEN '3' WHEN '4' THEN '4'  " +
                        "  WHEN '5' THEN '5'  WHEN '6' THEN '6'  WHEN '7' THEN '7' WHEN '8' THEN '8'  " +
                        "  WHEN '9' THEN '9'  WHEN '10' THEN '10'  WHEN '11' THEN '11'  " +
                        "  WHEN '12' THEN '12'  END AS 'week', IFNULL( datas.totalUser, 0 ) totalUser  FROM (SELECT a.d weeks, b.totalUser  FROM ( " +
                        "  SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT " +
                        "6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT " +
                "  11 UNION ALL SELECT " +
                "  12 ) a LEFT JOIN ( SELECT date_format( us.constructionTime, '%m' ) AS datetime, COUNT( us.id ) AS totalUser  FROM t_patrolaccount us  WHERE " +
                " status<>'2' and proId=' " + sysUser.getProId() + "' and enterpriseId=' " + sysUser.getEnterprise().getId() + "' and areaId=' " + enterpriseProject.getId() + "'" +
                " and receiverId='" + teamId + "'" +
                "  AND DATE_FORMAT(us.constructionTime,'%Y') = '" + timeDate.substring(0, 4) + "' GROUP BY datetime) b ON a.d = b.datetime) datas ORDER BY datas.weeks ASC").list();

        MsgInfo msgInfo = new MsgInfo();
        String[] month2 = new String[list8.size()];
        String[] data2 = new String[list8.size()];
        for (int i = 0; i < list8.size(); i++) {
            month2[i] = list8.get(i)[0].toString()+"月";
            data2[i] = list8.get(i)[1].toString();
        }
        Map<String, String[]> map = new HashMap<>();
        map.put("month2",month2);
        map.put("data2",data2);
        msgInfo.setData(map);
        return msgInfo;
    }


    @ResponseBody
    @RequestMapping("findTeamPatrolByDay")
    public MsgInfo findTeamPatrolByDay(@RequestParam(value = "teamId", required = false) String teamId,
                                       @RequestParam(value = "timeDate", required = false) String timeDate){
        MsgInfo msgInfo = new MsgInfo();
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        EnterpriseProject enterpriseProject = (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        List<Object[]> list9 = patrolAccountService.getHibernateSession().createSQLQuery("SELECT d,IFNULL( b.pid, 0 ) pid " +
                " FROM( SELECT ADDDATE( y.FIRST, x.d - 1 ) AS d FROM ( SELECT " +
                "   1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT " +
                "   5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT " +
                "   9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT " +
                "   12 UNION ALL SELECT 13 UNION ALL SELECT " +
                "   14 UNION ALL SELECT 15 UNION ALL SELECT " +
                "   16 UNION ALL SELECT 17 UNION ALL SELECT " +
                "   18 UNION ALL SELECT " +
                "   19 UNION ALL SELECT " +
                "   20 UNION ALL SELECT " +
                "   21 UNION ALL SELECT " +
                "   22 UNION ALL SELECT " +
                "   23 UNION ALL SELECT " +
                "   24 UNION ALL SELECT " +
                "   25 UNION ALL SELECT " +
                "   26 UNION ALL SELECT " +
                "   27 UNION ALL SELECT " +
                "   28 UNION ALL SELECT " +
                "   29 UNION ALL SELECT " +
                "   30 UNION ALL SELECT " +
                "   31 ) x,( SELECT CONCAT( '"+timeDate+"', '-01' ) AS FIRST, DAY ( LAST_DAY( str_to_date( '"+timeDate+"', '%Y-%m' ))) AS last ) y" +
                " WHERE x.d <= y.last ) a " +
                " LEFT JOIN ( SELECT date( p.constructionTime ) AS datetime, COUNT( p.id ) AS pid FROM t_patrolaccount p WHERE " +
                " status<>'2' and proId='" + sysUser.getProId() + "' and enterpriseId='" + sysUser.getEnterprise().getId() + "' and areaId='" + enterpriseProject.getId() + "' " +
                " and receiverId='" + teamId + "' AND DATE_FORMAT(p.constructionTime,'%Y-%m') = '" + timeDate + "'" +
                " GROUP BY date(p.constructionTime)) b ON a.d = b.datetime  " +
                "ORDER BY date(a.d) ASC").list();

        String[] month3 = new String[list9.size()];
        String[] data3 = new String[list9.size()];
        Map<String, String[]> map1 = new HashMap<>();
        for (int i = 0; i < list9.size(); i++) {
            month3[i] = list9.get(i)[0].toString().substring(8).replaceFirst("^0*","")+"日";
            data3[i] = list9.get(i)[1].toString();
        }
        map1.put("month3",month3);
        map1.put("data3",data3);
        msgInfo.setData(map1);
        return msgInfo;
    }


    @ResponseBody
    @RequestMapping("selectByTeam")
    public Layui selectByTeam(@RequestParam(value = "areaId") Integer areaId) {
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getSession().getAttribute("om_user");
        DetachedCriteria criteria = DetachedCriteria.forClass(SysUser.class);
        criteria.add(Restrictions.eq("areaid", areaId));
        criteria.add(Restrictions.eq("projectid", sysUser.getProId()));
        criteria.add(Restrictions.eq("enterprise", sysUser.getEnterprise()));
        criteria.createAlias("sysRole.sysRoleCode", "code");
        criteria.add(Restrictions.eq("code.role_number", "construction"));
        List<SysUser> list = openAccountService.findByConditions(criteria);
        return Layui.data(0, "查询成功", list);
    }
}
